#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"
#define CREAT  "create table if not exists studen(id INTEGER primary key AUTOINCREMENT,name text, ip varchar(32) UNIQUE);"

#define INSERT "insert into studen values(NULL, '%s','%s');"
#define DELETE "delete  from studen where id=%d;"
#define SELECT "select * from studen;"
#define UPDATE "update studen set name='wanwu' where id=%d;"     //修改
#define DBNAME "mysql.db"

int f = 0;
void insert(sqlite3 *ppdb, char *sql)        //插入数据
{

	char *err = NULL;
	int ret = sqlite3_exec(ppdb, sql, NULL, NULL, &err); //err 数据库错误输出
	if(ret != SQLITE_OK)
	{
		printf("%s\n",err);
		perror("insert fail");
		return ;
	}	

}
void sele(sqlite3 *ppdb, char *sql)     //查询数据
{
	char **result = NULL;
	char *err     = NULL;
	int  row      = 0;
	int  col      = 0;
	int ret = sqlite3_get_table(ppdb, sql, &result, &row, &col, &err);
	if(ret != SQLITE_OK)
	{
		printf("%s\n",err);
		return ;
	}
	int i=0;
	int j=0;
	for(i=0; i<(row+1); i++)
	{
		for(j=0; j<col; j++)
		{
			printf("%s\t",result[i*col+j]);
		}
		printf("\n");
	}	
	sqlite3_free_table(result);
}
void delete(sqlite3 *ppdb,char *sql) //删除数据
{
	int ret = sqlite3_exec(ppdb, sql, NULL, NULL, NULL);
	if(ret != SQLITE_OK)
	{
		perror("delect fail");
		return ;
	}
}
char *spr(void)
{
	char *inser = calloc(1,128);
	char name[32] = {0};
	char ip[32] = {0};

	printf("请输入名字:");scanf("%s", name);
	printf("请输入IP:");scanf("%s", ip);
	sprintf(inser, INSERT, name, ip);
	return inser;
}

int main(void)
{
	sqlite3 *ppdb;
	int ret = sqlite3_open(DBNAME, &ppdb);  //打开数据库
	if(ret != SQLITE_OK)
	{
		perror("open fail");
		return -1;
	}	
	
	ret = sqlite3_exec(ppdb, CREAT, NULL, NULL, NULL); //创建表 第三个参数为回调函数，第四个参数为回调函数第1个参赛，第五个参数为数据库出错处理
	if(ret != SQLITE_OK)
	{
		perror("creat fail");
		return -1;
	}
	int number;
	while(1)
	{
		printf("*****1.插入数据*****\n");
		printf("*****2.查询数据*****\n");
		printf("*****3.删除数据*****\n");
		printf("*****0.  退出  *****\n");

		scanf("%d",&number);
		char *selet=SELECT;
		char *delet=DELETE;
		char *inser = NULL;
		switch(number)
		{
			case 1:
				inser = spr();
			      	insert(ppdb, inser);break;
			case 2: 
				sele(ppdb, selet);f=0;break;
			case 3:
				printf("请输入删除对id:\n");
				scanf("%d",&number);
				char delet[256] = {0};
				sprintf(delet,DELETE,number);
				delete(ppdb, delet);break;
		}
		if(number == 0)
			break;
	}	

	ret = sqlite3_close(ppdb); //关闭数据库

	return 0;
}
